﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for EventBO
    /// </summary>
    public class EventBO : DBConnectionBO
    {
        public EventBO()
            : base()
        {
        }

        /// <summary>
        /// Get list event
        /// </summary>
        /// <returns></returns>
        public List<Event> GetListEvent()
        {
            var listEvents = new List<Event>();
            const string query = "SELECT TOP 100 * FROM [PrivateBlog].[dbo].[Events]";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            this.CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var eEvent = new Event
                    {
                        Id = (string)dataTable.Rows[i][0],
                        Name = (string)dataTable.Rows[i][1],
                        ImageThumbnail = (string)dataTable.Rows[i][2],
                        Description = (string)dataTable.Rows[i][3],
                        Content = (string)dataTable.Rows[i][4],
                        Author = (string)dataTable.Rows[i][5],
                        DateOpen = (DateTime)dataTable.Rows[i][6],
                        DateClose = (DateTime)dataTable.Rows[i][7],
                        IdUser = (int)dataTable.Rows[i][8],
                        Address = (string)dataTable.Rows[i][9],
                        Invite = (string)dataTable.Rows[i][10],
                        LastDateUpdated = (DateTime)dataTable.Rows[i][11],
                        LastUpdatedBy = (string)dataTable.Rows[i][12],
                        Status = Convert.ToInt32(dataTable.Rows[i][13])
                    };
                listEvents.Add(eEvent);
            }
            return listEvents;
        }

        /// <summary>
        /// Add a new event
        /// </summary>
        /// <param name="eEvent"></param>
        /// <returns></returns>
        public int AddEvent(Event eEvent)
        {
            var query =
                "INSERT INTO [PrivateBlog].[dbo].[Events]([Id],[Name],[ImageThumbnail],[Description],[Content]," +
                "[Author],[DateOpen],[DateClose],[IdUser],[Address],[Invite],[LastDateUpdated],[LastUpdatedBy],[Status]) VALUES (" +
                "'" + eEvent.Id + "', N'" +
                eEvent.Name + "', '" +
                eEvent.ImageThumbnail + "', N'" +
                eEvent.Description + "', N'" +
                eEvent.Content + "', N'" +
                eEvent.Author + "', " +
                eEvent.DateOpen + ", " +
                eEvent.DateClose + ", " +
                eEvent.IdUser + ", N'" +
                eEvent.Address + "', N'" +
                eEvent.Invite + "', " +
                "GETDATE(), N'" +
                eEvent.LastUpdatedBy + "', " +
                eEvent.Status +
                " ) ";
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }


        /// <summary>
        /// Edit a event
        /// </summary>
        /// <param name="eEvent"></param>
        /// <returns></returns>
        public int EditEvent(Event eEvent)
        {
            var query = "UPDATE [PrivateBlog].[dbo].[SlideShows]" +
                        "SET Name = N'" + eEvent.Name + "', " +
                        "ImageThumbnail = " + eEvent.ImageThumbnail + ", " +
                        "Description = N'" + eEvent.Description + "', " +
                        "Content = N'" + eEvent.Content + "', " +
                        "Author = N'" + eEvent.Author + "', " +
                        "DateOpen = " + eEvent.DateOpen + ", " +
                        "DateClose = " + eEvent.DateClose + ", " +
                        "IdUser = " + eEvent.IdUser +
                        "Address = N'" + eEvent.Address + "', " +
                        "Invite = N'" + eEvent.Invite + "', " +
                        "LastDateUpdated = GETDATE(), " +
                        "LastUpdatedBy = N'" + eEvent.LastUpdatedBy + "', " +
                        "Status = " + eEvent.Status + ", " +
                        "WHERE Id like '" + eEvent.Id + "'";
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a event
        /// </summary>
        /// <param name="eEvent"></param>
        /// <returns></returns>
        public int DeleteEvent(Event eEvent)
        {
            var query = "DELETE FROM [PrivateBlog].[dbo].[Events] " +
                        " WHERE Id like '" + eEvent.Id + "'";
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }
    }
}